package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.faces.context.FacesContext;
import javax.servlet.http.HttpSession;


import org.apache.tomcat.dbcp.dbcp.BasicDataSource;



public class UserBean {
	private static final String notLoggedInMsg = "Нисте пријављени на систем. Можете направити профил или се пријавити на страници ";
	
	private String topBarDisplayText;
	private String latestMsg;
	private String creationMsg;
	private String newCompanyMsgs;
	private boolean logged;
	private String username;
	private String password;
	
	// registrated user & company
	private String name;
	private String surname;
	private String address;
	private String city;
	private String phone;
	private String mail;
	private Double buyingParam;
	private boolean isBlocked;
	private int unusedRes;
	private int userId;
	// company
	//private String companyPass;
	private String web;
	private String pib;
	private String giro;
	private String activityDescription;
	private String type1, type2, type3;
	private String activity1, activity2, activity3;
	private int type1id, type2id, type3id, activity1id, activity2id, activity3id;
	private int numberOfActivities;
	
	private String newPass;
	private String newChangingPass;
	private String newUser;
	
	private String changingPassMsg;
	BasicDataSource ds;
	//
	private CompanyActivities companyActivities;
	private PossibleActions possibleActions;
	private Offer newOffer;
	private Offer editingOffer;
	private DisplayingOffers displayingOffers;
	
	private AdminBean admin;
	
	private String wantedLink;

	public UserBean()
	{
		topBarDisplayText = notLoggedInMsg;
		logged = false;
		
		//database setup
		//InitialContext ctxt = new InitialContext();
		//DataSource ds = (DataSource)ctxt.lookup("jdbc:mysql://localhost:3306/reservation");
		
		ds = new BasicDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/reservation");
		ds.setUsername("djordjevukovic");
		ds.setPassword("piapass");
		
		//
		companyActivities = new CompanyActivities(ds);
		
		displayingOffers = new DisplayingOffers(ds, this);
		displayingOffers.loadDailyOffers();
	}
	
	public String profileManagement()
	{
		if(logged)
		{
			return "profileSettings";
		} 
		else
		{
			return "newProfile";
		}
	}
	
	public String login()
	{
		
		Connection cnn = null;
		ResultSet rs = null;
		String pStat = "SELECT * FROM users WHERE username=? AND password=?";
		PreparedStatement stat = null;
		try {
						
			cnn = ds.getConnection();
			
			stat = cnn.prepareStatement(pStat);
			stat.setString(1, username);
			stat.setString(2, password);
			
			rs = stat.executeQuery();
			
			if(rs.next())
			{
				userId = rs.getInt("idUsers");
				String type = rs.getString("userType");
				possibleActions = new PossibleActions(type, ds);
				username = rs.getString("username");
				
				if(type.equals("R"))
				{
					pStat = "SELECT * FROM registrateduser WHERE idRegistratedUser = " + userId;
					stat = cnn.prepareStatement(pStat);
					ResultSet usersRs = stat.executeQuery();
					usersRs.next();
					if(usersRs.getBoolean("isBlocked"))
					{
						this.isBlocked = true;
						latestMsg = "Ваш профил је блокиран";
						username = "";
						password = "";
						logged = false;
						return "newprofile";
					} 
					else
					{
						name = usersRs.getString("name");
						surname = usersRs.getString("surname");
						address = usersRs.getString("address");
						city = usersRs.getString("city");
						phone = usersRs.getString("phone");
						mail = usersRs.getString("mail");
						buyingParam = usersRs.getDouble("buyingParameter");
						unusedRes = usersRs.getInt("unusedReservations");
						
						topBarDisplayText = "Добродошли " + name + " " + surname + ". Пријављени сте као " + username + ". Профил можете контролисати на страници ";
						logged = true;
						displayingOffers = new DisplayingOffers(ds, this);
						displayingOffers.loadDailyOffers();
						return "loggedUser";
					}
					
				}
				else if(type.equals("C"))
				{
					
					
					pStat = "SELECT * FROM company WHERE idCompany = " + userId;
					stat = cnn.prepareStatement(pStat);
					ResultSet compRs = stat.executeQuery();
					compRs.next();
					
					name = compRs.getString("name");
					address = compRs.getString("address");
					city = compRs.getString("city");
					phone = compRs.getString("phone");
					mail = compRs.getString("phone");
					web = compRs.getString("webSite");
					pib = compRs.getString("PIB");
					giro = compRs.getString("giroAccount");
					activityDescription = compRs.getString("activityDescription");
					//company type 1
					type1id = compRs.getInt("companyType1");
					Statement typeStat = cnn.createStatement();
					String typeQuery = "SELECT CT.typeName, CA.idCompanyActivity, CA.activityName FROM companytype CT, companyactivity CA WHERE (CA.idCompanyActivity = CT.idCompanyActivity) " +
							"AND CT.idCompanyType = " + type1id;
					ResultSet typeRs = typeStat.executeQuery(typeQuery);
					typeRs.next();
					type1 = typeRs.getString(1);
					activity1 = typeRs.getString(3);
					activity1id = typeRs.getInt(2);
					// company type 2
					type2id = compRs.getInt("companyType2");
					typeStat = cnn.createStatement();
					typeQuery = "SELECT CT.typeName, CA.idCompanyActivity, CA.activityName FROM companytype CT, companyactivity CA WHERE (CA.idCompanyActivity = CT.idCompanyActivity) " +
							"AND CT.idCompanyType = " + type2id;
					typeRs = typeStat.executeQuery(typeQuery);
					if(typeRs.next())
					{
						type2 = typeRs.getString(1);
						activity2 = typeRs.getString(3);
						activity2id = typeRs.getInt(2);
					}
					//company type 3
					type3id = compRs.getInt("companyType3");
					typeStat = cnn.createStatement();
					typeQuery = "SELECT CT.typeName, CA.idCompanyActivity, CA.activityName FROM companytype CT, companyactivity CA WHERE (CA.idCompanyActivity = CT.idCompanyActivity) " +
							"AND CT.idCompanyType = " + type3id;
					typeRs = typeStat.executeQuery(typeQuery);
					if(typeRs.next())
					{
						type3 = typeRs.getString(1);
						activity3 = typeRs.getString(3);
						activity3id = typeRs.getInt(2);
					}
					//newOffer = new Offer(ds, this);
					topBarDisplayText = "Добродошли " + name + ". Профил можете контролисати на страници ";
					logged = true;
					displayingOffers = new DisplayingOffers(ds, this);
					displayingOffers.loadDailyOffers();
					//return "loggedCompany";
					return "loggedUser";
				}
				else if(type.equals("A"))
				{
					name = "Admin";
					surname = "Admin";
					username = "admin";
					logged = true;
					return "loggedUser";
				}
			}
			
			latestMsg = "Унето корисничко име и лозинка нису исправни. Покушајте поново.";
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if(stat != null)
			{
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(cnn != null)
			{
				try {
					cnn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		
		return "reservations";
	}

	public String createNewUser()
	{
		boolean exists = checkIfUserExists(newUser, newPass);
		if(exists)
		{
			creationMsg = "Већ постоји корисник са унетим корисничким именом. Покушајте поново.";
			return null;
		}
		
		Connection cnn = null;
		PreparedStatement stat = null;
		
		try {
			cnn = ds.getConnection();
			String pStat = "INSERT INTO users(username, password, userType) VALUES(?, ?, 'R')";
			stat = cnn.prepareStatement(pStat, Statement.RETURN_GENERATED_KEYS);
			stat.setString(1, newUser);
			stat.setString(2, newPass);
			stat.executeUpdate();
			ResultSet keys = stat.getGeneratedKeys();
			keys.next();
			int primaryKey = keys.getInt(1);
			//insert into registratedusers table
			pStat = "INSERT INTO registrateduser VALUES(?, ?, ?, ?, ? , ?, ?, 2, false, 0)";
			stat = cnn.prepareStatement(pStat);
			stat.setInt(1, primaryKey);
			stat.setNString(2, name);
			stat.setNString(3, surname);
			stat.setNString(4, address);
			stat.setNString(5, city);
			stat.setNString(6, phone);
			stat.setNString(7, mail);
			stat.executeUpdate();
			
			creationMsg = "Креирали сте новог корисника са корисничким именом " + newUser + ". Сада се можете пријавити.";
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if(cnn != null)
			{
				try {
					cnn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(stat != null)
			{
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	
	private boolean checkIfUserExists(String username, String password)
	{
		Connection cnn = null;
		Statement stat = null;
		ResultSet rs = null;
		
		try {
			cnn = ds.getConnection();
			stat = cnn.createStatement();
			rs = stat.executeQuery("SELECT * FROM users WHERE username='" + username + "' AND password = '" + password + "'");
			if(rs.next())
			{
				return true;
			}
			else
			{
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if(cnn != null)
			{
				try {
					cnn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(stat != null)
			{
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(rs != null)
			{
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return false;
		
	}
	
	public String changePassword()
	{
		Connection cnn = null;
		Statement stat = null;
		
		try {
			cnn = ds.getConnection();
			int userKey = passwordExists(newPass, cnn);
			if(userKey == 0)
			{
				changingPassMsg = "Погрешно је унета тренутна лозинка овог профила";
				return null;
			}
			else
			{
				stat = cnn.createStatement();
				stat.executeUpdate("UPDATE users SET password='" + newChangingPass + "' WHERE idUsers=" + userKey);
				changingPassMsg = "Лозинка је успешно промењена";
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if(cnn != null)
			{
				try {
					cnn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(stat != null)
			{
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return null;
	}
	
	private int passwordExists(String password, Connection cnn) throws SQLException
	{
		PreparedStatement stat = cnn.prepareStatement("SELECT * FROM users WHERE password = ?");
		stat.setString(1, password);
		ResultSet rs = stat.executeQuery();
		if(rs.next())
		{
			return rs.getInt("idUsers");
		}
		else
		{
			return 0;
		}
	}
	
	public String addActivity()
	{
		if(numberOfActivities == 3) return null;
		int typeId = companyActivities.getTypeSelected();
		switch(numberOfActivities)
		{
		case 0:
		{
			type1id = typeId;
			break;
		}
		case 1:
		{
			type2id = typeId;
			break;
		}
		case 2:
		{
			type3id = typeId;
			break;
		}
		}
		numberOfActivities++;
		return null;
	}
	
	public String registerCompany()
	{
		newCompanyMsgs = "";
		boolean exists = checkIfUserExists(newUser, newPass);
		if(exists)
		{
			newCompanyMsgs = "Већ постоји корисник са траженим корисничким именом";
			return null;
		}
		if(numberOfActivities < 1)
		{
			newCompanyMsgs = "Морате одабрати макар једну делатност";
			return null;
		}
		Connection cnn = null;
		PreparedStatement stat = null;
		
		String pQuery = "INSERT INTO requiredcompanies(username, password, name, address, city, phone, mail, webSite, PIB, giroAccount, activityDescription, companyType1, companyType2" +
				", companyType3) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		try {
			cnn = ds.getConnection();
			stat = cnn.prepareStatement(pQuery);
			//assign values
			stat.setNString(1, newUser);
			stat.setNString(2, newPass);
			stat.setNString(3, name);
			stat.setNString(4, address);
			stat.setNString(5, city);
			stat.setNString(6, phone);
			stat.setNString(7, mail);
			stat.setNString(8, web);
			stat.setNString(9, pib);
			stat.setNString(10, giro);
			stat.setNString(11, activityDescription);
			stat.setInt(12, type1id);
			
			if(type2id != 0)
			{
				stat.setInt(13, type2id);
			}
			else
			{
				stat.setNull(13, java.sql.Types.INTEGER);
			}
			
			if(type3id != 0)
			{
				stat.setInt(14, type3id);
			}
			else
			{
				stat.setNull(14, java.sql.Types.INTEGER);
			}
			
			stat.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if(cnn != null)
			{
				try {
					cnn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(stat != null)
			{
				try {
					stat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	public String goToLink()
	{
		if(wantedLink.equals("novaponuda"))
		{
			newOffer = new Offer(ds, this);
			return "novaponuda";
		}
		else if(wantedLink.equals("mojeponude"))
		{
			//to do
			newOffer = new Offer(ds, this);
			newOffer.loadMyOffers();
			return "mojeponude";
		}
		else if(wantedLink.equals("mojerezervacije"))
		{
			newOffer = new Offer(ds, this);
			newOffer.loadMyReservations(false);
			newOffer.setResType("Резервација");
			return "reservations";
		}
		else if(wantedLink.equals("mojekupovine"))
		{
			newOffer = new Offer(ds, this);
			newOffer.loadMyReservations(true);
			newOffer.setResType("Резервација");
			return "reservations";
		}
		else if(wantedLink.equals("potvrdarezervacija"))
		{
			admin = new AdminBean(ds);
			admin.loadReservations();
			return "confirmreservations";
		}
		else if(wantedLink.equals("potvrdaponudjaca"))
		{
			admin = new AdminBean(ds);
			admin.loadCompanies();
			return "requestedcompanies";
		}
		else if(wantedLink.equals("topponude"))
		{
			admin = new AdminBean(ds);
			admin.loadTopOffers();
			return "topoffers";
		}
		else if(wantedLink.equals("topkorisnici"))
		{
			admin = new AdminBean(ds);
			admin.loadTopUsers();
			return "topusers";
		}
		else if(wantedLink.equals("topponudjaci"))
		{
			admin = new AdminBean(ds);
			admin.loadTopCompanies();
			return "topcompanies";
		}
		return wantedLink;
	}
	
	public String logout()
	{
		logged = false;
		FacesContext context = FacesContext.getCurrentInstance();
	    HttpSession session = (HttpSession) context.getExternalContext().getSession(false);

	    session.invalidate();
		return "index";
	}
	
	public String goToDaily()
	{
		displayingOffers = new DisplayingOffers(ds, this);
		displayingOffers.loadDailyOffers();
		return "index";
	}
	
	public String goToCurrent()
	{
		displayingOffers = new DisplayingOffers(ds, this);
		//load current offers
		displayingOffers.loadCurrentOffers();
		return "current";
	}
	
	public String gotToAll()
	{
		displayingOffers = new DisplayingOffers(ds, this);
		//go to all
		return "all";
	}
	
	
	public String goToLogIn()
	{
		return "login";
	}
	public boolean isLogged() {
		return logged;
	}

	public void setLogged(boolean isLogged) {
		this.logged = isLogged;
	}

	public String getTopBarDisplayText() {
		return topBarDisplayText;
	}

	public void setTopBarDisplayText(String topBarDisplayText) {
		this.topBarDisplayText = topBarDisplayText;
	}
	
	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getLatestMsg() {
		return latestMsg;
	}

	public void setLatestMsg(String latestMsg) {
		this.latestMsg = latestMsg;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSurname() {
		return surname;
	}

	public void setSurname(String surname) {
		this.surname = surname;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getMail() {
		return mail;
	}

	public void setMail(String mail) {
		this.mail = mail;
	}

	public boolean isBlocked() {
		return isBlocked;
	}

	public void setBlocked(boolean isBlocked) {
		this.isBlocked = isBlocked;
	}

	public int getUnusedRes() {
		return unusedRes;
	}

	public void setUnusedRes(int unusedRes) {
		this.unusedRes = unusedRes;
	}

	public int getUserId() {
		return userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}

	public static String getNotloggedinmsg() {
		return notLoggedInMsg;
	}

	public String getWeb() {
		return web;
	}

	public void setWeb(String web) {
		this.web = web;
	}

	public String getPib() {
		return pib;
	}

	public void setPib(String pib) {
		this.pib = pib;
	}

	public String getGiro() {
		return giro;
	}

	public void setGiro(String giro) {
		this.giro = giro;
	}

	public String getActivityDescription() {
		return activityDescription;
	}

	public void setActivityDescription(String activity) {
		this.activityDescription = activity;
	}

	public String getType1() {
		return type1;
	}

	public void setType1(String type1) {
		this.type1 = type1;
	}

	public String getType2() {
		return type2;
	}

	public void setType2(String type2) {
		this.type2 = type2;
	}

	public String getType3() {
		return type3;
	}

	public void setType3(String type3) {
		this.type3 = type3;
	}

	public String getSubtype1() {
		return activity1;
	}

	public void setSubtype1(String subtype1) {
		this.activity1 = subtype1;
	}

	public String getSubtype2() {
		return activity2;
	}

	public void setSubtype2(String subtype2) {
		this.activity2 = subtype2;
	}

	public String getSubtype3() {
		return activity3;
	}

	public void setSubtype3(String subtype3) {
		this.activity3 = subtype3;
	}

	public BasicDataSource getDs() {
		return ds;
	}

	public void setDs(BasicDataSource ds) {
		this.ds = ds;
	}

	public String getCreationMsg() {
		return creationMsg;
	}

	public void setCreationMsg(String creationMsg) {
		this.creationMsg = creationMsg;
	}

	public String getNewPass() {
		return newPass;
	}

	public void setNewPass(String newPass) {
		this.newPass = newPass;
	}

	public String getNewUser() {
		return newUser;
	}

	public void setNewUser(String newUser) {
		this.newUser = newUser;
	}

	public String getActivity1() {
		return activity1;
	}

	public void setActivity1(String activity1) {
		this.activity1 = activity1;
	}

	public String getActivity2() {
		return activity2;
	}

	public void setActivity2(String activity2) {
		this.activity2 = activity2;
	}

	public String getActivity3() {
		return activity3;
	}

	public void setActivity3(String activity3) {
		this.activity3 = activity3;
	}

	public int getType1id() {
		return type1id;
	}

	public void setType1id(int type1id) {
		this.type1id = type1id;
	}

	public int getType2id() {
		return type2id;
	}

	public void setType2id(int type2id) {
		this.type2id = type2id;
	}

	public int getType3id() {
		return type3id;
	}

	public void setType3id(int type3id) {
		this.type3id = type3id;
	}

	public int getActivity1id() {
		return activity1id;
	}

	public void setActivity1id(int activity1id) {
		this.activity1id = activity1id;
	}

	public int getActivity2id() {
		return activity2id;
	}

	public void setActivity2id(int activity2id) {
		this.activity2id = activity2id;
	}

	public int getActivity3id() {
		return activity3id;
	}

	public void setActivity3id(int activity3id) {
		this.activity3id = activity3id;
	}

	public String getNewChangingPass() {
		return newChangingPass;
	}

	public void setNewChangingPass(String newChangingPass) {
		this.newChangingPass = newChangingPass;
	}

	public String getChangingPassMsg() {
		return changingPassMsg;
	}

	public void setChangingPassMsg(String changingPassMsg) {
		this.changingPassMsg = changingPassMsg;
	}

	public CompanyActivities getCompanyActivities() {
		return companyActivities;
	}

	public void setCompanyActivities(CompanyActivities companyActivities) {
		this.companyActivities = companyActivities;
	}

	public int getNumberOfActivities() {
		return numberOfActivities;
	}

	public void setNumberOfActivities(int numberOfActivities) {
		this.numberOfActivities = numberOfActivities;
	}

	public String getNewCompanyMsgs() {
		return newCompanyMsgs;
	}

	public void setNewCompanyMsgs(String newCompanyMsgs) {
		this.newCompanyMsgs = newCompanyMsgs;
	}

	public PossibleActions getPossibleActions() {
		return possibleActions;
	}

	public void setPossibleActions(PossibleActions possibleActions) {
		this.possibleActions = possibleActions;
	}

	public Offer getNewOffer() {
		return newOffer;
	}

	public void setNewOffer(Offer newOffer) {
		this.newOffer = newOffer;
	}

	public String getWantedLink() {
		return wantedLink;
	}

	public void setWantedLink(String wantedLink) {
		this.wantedLink = wantedLink;
	}

	public Offer getEditingOffer() {
		return editingOffer;
	}

	public void setEditingOffer(Offer editingOffer) {
		this.editingOffer = editingOffer;
	}

	public DisplayingOffers getDisplayingOffers() {
		return displayingOffers;
	}

	public void setDisplayingOffers(DisplayingOffers displayingOffers) {
		this.displayingOffers = displayingOffers;
	}

	public void setBuyingParam(Double buyingParam) {
		this.buyingParam = buyingParam;
	}

	public Double getBuyingParam() {
		return buyingParam;
	}

	public int getBuyingParamRounded()
	{
		return (int)Math.floor(buyingParam);
	}

	public AdminBean getAdmin() {
		return admin;
	}

	public void setAdmin(AdminBean admin) {
		this.admin = admin;
	}
	
	
}
